package org.barman.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.barman.exception.PersistenceException;
import org.barman.exception.ValidationException;
import org.barman.pojo.Item;
import org.barman.util.DbHelper;

public class ItemDAO implements DAO<Item> {

	@Override
	public void insert(Item item) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection.prepareStatement(""
					+ "insert into item(name, price, created)" + " values(?, ?, ?)");
			preparedStatement.setString(1, item.getName());
			preparedStatement.setBigDecimal(2, item.getPrice());
			preparedStatement.setDate(3, item.getCreated());

			if (preparedStatement.executeUpdate() == 0) {
				throw new PersistenceException("INSERT ITEM ERROR.");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("INSERT ITEM ERROR", e.getMessage(), e);
		}
	}

	@Override
	public void update(Item item) throws PersistenceException, ValidationException {

		if (item.getId() == null) {
			throw new ValidationException("ID NOT SET");
		}

		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("update item set name = ?, price = ?, created = ?, modified = ? where id = ?");
			preparedStatement.setString(1, item.getName());
			preparedStatement.setBigDecimal(2, item.getPrice());
			preparedStatement.setDate(3, item.getCreated());
			preparedStatement.setDate(4, item.getModified());
			preparedStatement.setInt(5, item.getId());

			// TODO update order-item references in order_has_item table

			if (preparedStatement.executeUpdate() == 0) {
				throw new PersistenceException("UPDATE ITEM ERROR.");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("UPDATE ITEM ERROR.", e.getMessage(), e);
		}
	}

	@Override
	public Item get(int itemId) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("select id, name, price, created, modified from item where id = ?");
			preparedStatement.setInt(1, itemId);
			ResultSet resultSet = preparedStatement.executeQuery();

			Item item = new Item();
			while (resultSet.next()) {
				item.setId(resultSet.getInt(1));
				item.setName(resultSet.getString(2));
				item.setPrice(resultSet.getBigDecimal(3));
				item.setCreated(resultSet.getDate(4));
				item.setModified(resultSet.getDate(5));
			}

			return item;

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("GET ITEM ERROR", e.getMessage(), e);
		}

	}

	@Override
	public List<Item> getAll() throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("select id, name, price, created, modified from item");
			ResultSet resultSet = preparedStatement.executeQuery();

			List<Item> allItems = new ArrayList<Item>();
			while (resultSet.next()) {

				Item item = new Item();
				item.setId(resultSet.getInt(1));
				item.setName(resultSet.getString(2));
				item.setPrice(resultSet.getBigDecimal(3));
				item.setCreated(resultSet.getDate(4));
				item.setModified(resultSet.getDate(5));

				allItems.add(item);
			}

			return allItems;

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("GET ALL ITEMS ERROR.", e.getMessage(), e);
		}
	}

	@Override
	public void delete(int itemId) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection.prepareStatement("delete from item where id = ?");
			preparedStatement.setInt(1, itemId);

			if (preparedStatement.executeUpdate() == 0) {
				throw new PersistenceException("DELETE ITEM ERROR.");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("DELETE ITEM ERROR.", e.getMessage(), e);
		}
	}

	public List<Item> getAllByOrderId(int orderId) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("select i.id, i.name, i.price, i.created, i.modified from item i, order_has_item oi where oi.order_id = ? and i.id = oi.item_id");
			preparedStatement.setInt(1, orderId);
			ResultSet resultSet = preparedStatement.executeQuery();

			List<Item> allItems = new ArrayList<Item>();
			while (resultSet.next()) {

				Item item = new Item();
				item.setId(resultSet.getInt(1));
				item.setName(resultSet.getString(2));
				item.setPrice(resultSet.getBigDecimal(3));
				item.setCreated(resultSet.getDate(4));
				item.setModified(resultSet.getDate(5));

				allItems.add(item);
			}

			return allItems;

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("GET ALL ITEMS BY ORDER ID ERROR.", e.getMessage(), e);
		}
	}
}
